<?php

/***
 * Class DatabaseDriver provides a layer of database
 * abstraction for MySql connections.
 ***/
class CW_DatabaseDriver implements CW_DriverInterface
{
	/***
	 * Stores the connection resource id
	 * @type Resource Id
	 ***/
	private $connection_id;


	/***
	 * Stores the number of queries executed
	 * during a request.
	 * @type Integer
	 ***/
	private $query_count;


	/***
	 * Query execution times.
	 * @type Array
	 ***/
	private $query_times;


	/***
	 * Stores all query strings during a request.
	 * @type Array
	 ***/
	private $query_stack;


	/***
	 * Contains the current sql string.
	 * @type Integer
	 ***/
	private $query_current;


	// ! Constructor

	/***
	 * Insantiates object.
	 * @param $Reg System registry object
	 ***/
	public function __construct()
	{
		$this->connection_id = null;

		$this->query_count = 0;
		$this->query_times = array();
		$this->query_stack = array();
		$this->query_current = '';

		$this->db_host = '';
		$this->db_port = '';
		$this->db_user = '';
		$this->db_pass = '';
		$this->db_dbname = '';
	}


   // ! Executor

   /**
	* Attempts to establish a connection to a
	* specified database.
	*
	* @param $host   Location of the database server
	* @param $port   Default port to connect to
	* @param $user   User name for database account
	* @param $pass   User password for database account
	* @param $dbname Name of the database to connect to
	* @return Boolean
	*/
	public function connect($host, $port, $user, $pass, $dbname, $debug = true)
	{
		if(false == $this->connection_id = @mysql_connect($host, $user, $pass, $dbname, $port))
		{
			if($debug)
			{
				throw new CoreException("Could not connect to '{$host}:{$port}' on database '{$dbname}' with user '{$user}'!");
			}

			return false;
		}

		$this->db_host = $host;
		$this->db_port = $port;
		$this->db_user = $user;
		$this->db_pass = $pass;
		$this->db_dbname = $dbname;

		return true;
	}


   // ! Accessor

   /**
	* Executes a query
	*
	* @param $sql The query string to execute
	* @return Mixed
	*/
	public function query($sql)
	{
		$this->query_count++;
		$this->query_current = $sql;
		$this->query_stack[] = $sql;

		if(false == $result = @mysql_query($sql, $this->connection_id))
		{
			throw new CoreException("Database error #" . $this->returnErrorCode() . ": " . $this->returnErrorMessage());
		}

		$this->query_current = '';

		return new CW_ResultsIterator($result);
	}


   // ! Accessor

   /**
	* Returns a connection error code
	*
	* @param none
	* @return Mixed
	*/
	public function returnConnectionErrorCode()
	{
		return true;
	}


   // ! Accessor

   /**
	* Returns a connection error message
	*
	* @param none
	* @return Mixed
	*/
	public function returnConnectionErrorMessage()
	{
		return true;
	}


   // ! Accessor

   /**
	* Returns a query execution error code
	*
	* @param none
	* @return Mixed
	*/
	public function returnErrorCode()
	{
		return @mysql_errno($this->connection_id);
	}


   // ! Accessor

   /**
	* Returns a query execution error message
	*
	* @param none
	* @return Mixed
	*/
	public function returnErrorMessage()
	{
		return @mysql_error($this->connection_id);
	}


   // ! Executor

   /**
	* Closes the current database connection
	*
	* @param none
	* @return Mixed
	*/
	public function close()
	{
		return @mysql_close($this->connection_id);
	}


   // ! Accessor

   /**
	* Returns the status of the current database connection
	*
	* @param none
	* @return Boolean
	*/
	public function isConnected()
	{
		return $this->connection_id;
	}


   // ! Accessor

   /**
	* Returns the number of affected rows from the
	* last transaction.
	*
	* @param none
	* @return Integer
	*/
	public function getAffectedRows()
	{
		return @mysql_affected_rows($this->connection_id);
	}


   // ! Accessor

   /**
	* Returns the insert id from the last INSERT
	* query.
	*
	* @param none
	* @return Integer
	*/
	public function getInsertId()
	{
		return @mysql_insert_id($this->connection_id);
	}


   // ! Accessor

   /**
	* Returns the version of the database server.
	*
	* @param none
	* @return String
	*/
	public function getServerVersion()
	{
		$result = $this->query('SELECT VERSION() AS version');

		$version = $result->nextRow();

		return $version['version'];
	}


   // ! Executor

   /**
	* Escapes a string before adding it to the database
	*
	* @param none
	* @return Mixed
	*/
	public function escapeString($string)
	{
		if(false == get_magic_quotes_gpc())
		{
			$string = addslashes($string);
		}

		return @mysql_real_escape_string($string, $this->connection_id);
	}


   // ! Executor

   /**
	* Wraps single quotes around a single string value.
	*
	* @param $value Mixed value to format
	* @return Mixed
	*/
	private function formatSingleValue($value)
	{
		if(is_bool($value) || is_numeric($value))
		{
			$value = (int) $value;
		}
		else
		{
			$value = "'" . $this->escapeString($value) . "'";
		}

		return $value;
	}


   // ! Executor

   /**
	* Can format a single or multiple values passed in
	* an array.
	*
	* @param $value Mixed value to format
	* @return Mixed
	*/
	public function formatValues($value)
	{
		if(is_array($value))
		{
			$value_temp = array();

			foreach($value as $item)
			{
				$value_temp[] = $this->formatSingleValue($item);
			}

			return $value_temp;
		}

		return $this->formatSingleValue($value);
	}


   // ! Accessor

   /**
	* Used to create a simple INSERT query string.
	*
	* @param $columns Array with key => value pairs containing columns and their corresponding values.
	* @param $table   Name of the table to insert values into.
	* @return String
	*/
	public function simpleInsert($columns, $table)
	{
		$sql  = "INSERT INTO " . DB_PREFIX . "{$table} (";

		$sql .= implode(', ', array_keys($columns));

		$sql .= ') VALUES (';

		$values = array();

		foreach(array_values($columns) as $value)
		{
			$values[] = $this->formatValues($value);
		}

		$sql .= implode(', ', $values);

		$sql .= ')';

		return $this->query($sql);
	}


   // ! Accessor

   /**
	* Creates a simple DELETE query string.
	*
	* @param $table      Table name to remove rows from.
	* @param $conditions A multidimensional array containing conditions:
						 ARRAY('column'   => 'user_name',
							   'operator' => '=',
							   'match'    => 'bob')
	* @return String
	*/
	public function simpleDelete($table, $conditions)
	{
		$sql = 'DELETE FROM ' . DB_PREFIX . $table;

		if($conditions)
		{
			$sql .= " WHERE";

			$i = 0;
			$count = sizeof($conditions);

			foreach($conditions as $condition)
			{
				$i++;

				if(false == isset($condition['operator']))
				{
					$condition['operator'] = '=';
				}

				switch(trim(strtolower($condition['operator'])))
				{
					case 'in':
					case 'not in':

						$sql .= " {$condition['column']} {$condition['operator']} (" . implode(',', $this->formatValues($condition['match'])) . ")";

						break;

					default:

						$sql .= " {$condition['column']} {$condition['operator']} " . $this->formatValues($condition['match']);

						break;
				}

				if(isset($condition['appender']))
				{
					$sql .= " {$condition['appender']}";
				}
			}
		}

		return $this->query($sql);
	}


   // ! Accessor

   /**
	* Creates a simple UPDATE query string.
	*
	* @param $columns Array with key => value pairs containing columns and their corresponding values.
	* @param $table      Table name to remove rows from.
	* @param $conditions A multidimensional array containing conditions:
						 ARRAY('column'   => 'user_name',
							   'operator' => '=',
							   'match'    => 'bob')
	* @return String
	*/
	public function simpleUpdate($columns, $table, $conditions = array())
	{
		$sql = 'UPDATE ' . DB_PREFIX . $table . ' SET ';

		$temp = array();

		foreach($columns as $column => $value)
		{
			$temp[] = "{$column} = " . $this->formatValues($value);
		}

		$sql .= implode(', ', $temp);

		if($conditions)
		{
			$sql .= " WHERE";

			$i = 0;
			$count = sizeof($conditions);

			foreach($conditions as $condition)
			{
				$i++;

				if(false == isset($condition['operator']))
				{
					$condition['operator'] = '=';
				}

				switch(trim(strtolower($condition['operator'])))
				{
					case 'in':
					case 'not in':

						$sql .= " {$condition['column']} {$condition['operator']} (" . implode(',', $this->formatValues($condition['match'])) . ")";

						break;

					default:

						$sql .= " {$condition['column']} {$condition['operator']} " . $this->formatValues($condition['match']);

						break;
				}

				if(isset($condition['appender']))
				{
					$sql .= " {$condition['appender']}";
				}
			}
		}

		return $this->query($sql);
	}


   // ! Accessor

   /**
	* Creates a simple SELECT query string.
	*
	* @param $columns Array with key => value pairs containing columns and their corresponding aliases, if one exists.
	* @param $table      Table name to remove rows from.
	* @param $conditions A multidimensional array containing conditions:
						 ARRAY('column'   => 'user_name',
							   'operator' => '=',
							   'match'    => 'bob')
	* @param $limit      Array containing offset and limit numbers:
						 ARRAY('offset' => 10,
							   'limit'  => 100)
	* @param $order      A multidimensional array containing order values:
						 ARRAY('column' => 'user_id',
							   'alias'  => 'u',
							   'order'  => 'DESC')
	* @return String
	*/
	public function simpleSelect($columns, $table, $conditions = array(), $limit = array(), $order = array())
	{
		$sql = 'SELECT ';

		if($columns == '*')
		{
			$sql .= '*';
		}
		else
		{
			$i = 0;
			$count = sizeof($columns);

			foreach($columns as $column)
			{
				$i++;

				if(isset($column['alias']) && $column['alias'])
				{
					$sql .= "{$column['column']} AS {$column['alias']}";
				}
				else
				{
					$sql .= "{$column['column']}";
				}

				if($i != $count)
				{
					$sql .= ', ';
				}
			}
		}

		$sql .= ' FROM ' . DB_PREFIX . $table;

		if($conditions)
		{
			$sql .= " WHERE ";

			$i = 0;
			$count = sizeof($conditions);

			foreach($conditions as $condition)
			{
				$i++;

				if(false == isset($condition['1']))
				{
					$condition['1'] = '=';
				}

				switch(trim(strtolower($condition['1'])))
				{
					case 'in':
					case 'not in':

						$sql .= " {$condition['0']} {$condition['1']} (" . implode(',', $this->formatValues($condition['2'])) . ")";

						break;

					default:

						$sql .= " {$condition['0']} {$condition['1']} " . $this->formatValues($condition['2']);

						break;
				}

				if(isset($condition['3']))
				{
					$sql .= " {$condition['3']}";
				}
				elseif(false == isset($condition['3']) && $i + 1 == $count)
				{
					$sql .= ' AND';
				}
			}
		}

		if($order)
		{
			$sql .= " ORDER BY ";
			$i = 0;
			$count  = sizeof($order);

			foreach($order as $columns)
			{
				$i++;

				if(false == isset($columns['order']))
				{
					$columns['order'] = 'DESC';
				}

				$sql .= isset($columns['alias']) ? "{$columns['alias']}." : '';
				$sql .= "{$columns['column']} {$columns['order']}";

				if($i != $count)
				{
					$sql .= ', ';
				}
			}
		}

		if($limit)
		{
			$sql .= " LIMIT {$limit[1]}, {$limit[0]}";
		}

		return $this->query($sql);
	}


   // ! Accessor

   /**
	* Fetches a list of tables within the current database
	* and returns them as an array.
	*
	* @param none
	* @return Array
	*/
	public function getTableList($database)
	{
		$res = $this->query('SHOW TABLES');

		if(false == $res->getNumRows())
		{
			return array();
		}

		$tables = array();

		while($row = $res->nextRow())
		{
			$tables[] = $row["Tables_in_{$database}"];
		}

		return $tables;
	}


   // ! Accessor

   /**
	* Retrieves a listing of columns for a specified table.
	*
	* @param $table Name of table
	* @return Void
	*/
	public function getTableColumns($table)
	{
		$res = $this->query("EXPLAIN `{$table}`");

		if(false == $res->getNumRows())
		{
			return array();
		}

		$columns = array();

		while($row = $res->nextRow())
		{
			$columns[] = $row['Field'];
		}

		return $columns;
	}


   // ! Accessor

   /**
	* Returns 'show create table' sql for a specified table.
	*
	* @param $table_name Table to pull statement for
	* @return Mixed
	*/
	public function getCreateTableSql($table_name)
	{
		$res = $this->query("SHOW CREATE TABLE `{$table_name}`");

		if(false == $res->getNumRows())
		{
			return false;
		}

		$create = $res->nextRow();

		return $create['Create Table'];
	}


   // ! Accessor

   /**
	* Checks to see if a field exists within a specified table.
	*
	* @param $field_name Name of field to check.
	* @param $table_name Table belonging to the field.
	* @return Bool
	*/
	public function checkIfFieldExists($field_name, $table_name)
	{
		return in_array($field_name, $this->getTableColumns($table_name));
	}


   // ! Accessor

   /**
	* Removes a table from the database.
	*
	* @param $table_name Table to be removed.
	* @return Void
	*/
	public function dropTable($table_name)
	{
		if(in_array($table_name, $this->getTableList()))
		{
			return $this->query("DROP TABLE '`{$table_name}`");
		}

		return false;
	}


   // ! Accessor

   /**
	* Removes a field from specified table.
	*
	* @param $field_name Name of field to remove.
	* @param $table_name Table belonging to the field.
	* @return Bool
	*/
	public function dropField($field_name, $table_name)
	{
		if($this->checkIfFieldExists($field_name, $table_name))
		{
			return $this->query("ALTER TABLE `{$table_name}` DROP `{$field_name}`");
		}

		return true;
	}


   // ! Accessor

   /**
	* Adds a field to specified table.
	*
	* @param $table_name    Name of field to add.
	* @param $field_name    Table belonging to the field.
	* @param $field_type    Field data type.
	* @param $default_value Default value of new field.
	* @return Void
	*/
	public function addField($table_name, $field_name, $field_type, $default_value = '')
	{
		if($this->checkIfFieldExists($field_name, $table_name))
		{
			$this->dropField($field_name, $table_name);
		}

		return $this->query("ALTER TABLE `{$table_name}` ADD `{$field_name}` {$field_type} DEFAULT '{$default_value}'");
	}


   // ! Accessor

   /**
	* Optimizes a specified table.
	*
	* @param $table_name Table to optimize.
	* @return Void
	*/
	public function optimizeTable($table_name)
	{
		return $this->query("OPTIMIZE TABLE `{$table_name}`");
	}


   // ! Accessor

   /**
	* Returns the number of affected rows from the
	* last transaction.
	*
	* @param $class  Name of the class in which the query is stored.
	* @param $method Name of the method to execute.
	* @param $params Array containing parameters to be used by the requested method.
	* @return String
	*/
	public function dump($tables = '', $data = true, $drop = false)
	{
		$all_tables = $this->DB->getTableList($this->DB->db_dbname);

		if(false == $tables)
		{
			$tables = $all_tables;
		}
		else
		{
			$tables = explode('|', $tables);
		}

		foreach($tables as $table)
		{
			if(in_array($table, $all_tables))
			{
				if($drop)
				{
					$this->sql_buffer .= "DROP TABLE IF EXISTS `{$table}`;" . self::CLRF . self::CLRF;
				}

				$this->sql_buffer .= $this->DB->getCreateTableSql($table) . ';' . self::CLRF . self::CLRF;

				if($data)
				{
					$columns = '`' . implode('`,`', $this->DB->getTableColumns($table)) . '`';

					$result = $this->DB->query("SELECT {$columns} FROM `{$table}`");

					while($row = $result->nextRow())
					{
						$insert = "INSERT INTO {$table} ({$columns}) VALUES (";

						$values = array();

						foreach($row as $value)
						{
							$values[] = $this->DB->formatValues($value);
						}

						$insert .=  implode(', ', $values) . ');' . self::CLRF;

						$this->sql_buffer .= $insert;
					}

					$this->sql_buffer .= self::CLRF;
				}
			}
		}

		return $this->sql_buffer;
	}
}


/***
 * Class CW_ResultsIterator iterates over a result set provided
 * by a successful query.
 ***/

class CW_ResultsIterator implements CW_IteratorInterface
{
	/***
	 * Stores the resource id for the last executed query.
	 * @type Resource Id
	 ***/
	private $results_object;


	/***
	 * Determines the position within a result set.
	 * @type Integer
	 ***/
	private $index;


	// ! Constructor

	/***
	 * Insantiates object.
	 *
	 * @param $result_object Stores the resource id for the last executed query.
	 ***/
	public function __construct($results_object)
	{
		$this->results_object = $results_object;
		$this->index = -1;
	}


   // ! Accessor

   /**
	* Returns the next row in a result set as determined by
	* the 'index' instance variable.
	*
	* @param none
	* @return Mixed
	*/
	public function nextRow()
	{
		if($this->index >= -1 && $this->index < $this->getNumRows())
		{
			$this->index++;

			return @mysql_fetch_assoc($this->results_object);
		}

		$this->freeResult();

		return false;
	}


   // ! Accessor

   /**
	* Returns a field value from the current row or
	* another row by the optional $index pointer.
	*
	* @param $column The name of the column to return
	* @param $index  The index of the record to fetch
	* @return Array
	*/
	public function getColumnValue($column, $index = 0)
	{
		@mysql_data_seek($this->results_object, $index);

		$row = $this->getCurrentRow();

		$this->index++;

		@mysql_data_seek($this->results_object, $this->index);

		return $row[$column];
	}


   // ! Accessor

   /**
	* Returns the current row in a result set as
	* determined by the 'index' instance variable.
	*
	* @param none
	* @return Array
	*/
	public function getCurrentRow()
	{
		$row = $this->nextRow();

		$this->index--;

		@mysql_data_seek($this->results_object,$this->index);

		return $row;
	}


   // ! Accessor

   /**
	* Returns the number of rows retrieved by a result
	* set.
	*
	* @param none
	* @return Integer
	*/
	public function getNumRows()
	{
		return @mysql_num_rows($this->results_object);
	}


   // ! Accessor

   /**
	* Frees a result set from memory.
	*
	* @param none
	* @return Integer
	*/
	public function freeResult()
	{
		return @mysql_free_result($this->results_object);
	}


   // ! Mutator

   /**
	* Resets the 'index' instance variable.
	*
	* @param none
	* @return Boolean
	*/
	public function resetIndex()
	{
		$this->index = 0;

		return true;
	}
}

?>